package com.sigmatrix.h5.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.sigmatrix.h5.dao.DisDealerDao;
import com.sigmatrix.h5.dao.DisRebateScanCodeErrorDao;
import com.sigmatrix.h5.dao.DisTerminalShopDao;
import com.sigmatrix.h5.dao.ProductDao;
import com.sigmatrix.h5.entity.DisRebateScanCodeError;
import com.sigmatrix.h5.enums.CrmAcctType;
import com.sigmatrix.h5.exception.MessageException;
import com.sigmatrix.h5.utils.CommonUtils;
import com.sigmatrix.h5.utils.SimpleRowMapper;
import com.sigmatrix.h5.web.args.DealerInventoryPageParam;
import com.sigmatrix.h5.web.mode.DealerInventoryDayCount;
import com.sigmatrix.h5.web.mode.ErrorInfo;
import com.sigmatrix.sc.jdbc.common.dao.DaoImpl;

@Repository
public class DisRebateScanCodeErrorDaoImpl extends DaoImpl<DisRebateScanCodeError,Integer>  implements DisRebateScanCodeErrorDao{

	@Autowired
	private DisDealerDao disDealerDao;
	@Autowired
	private ProductDao productDao;
	@Autowired
	private DisTerminalShopDao disTerminalShopDao;
	
	@Override
	public List<DealerInventoryDayCount> findDayCountList(DealerInventoryPageParam vo) {
		List<Object> values = new ArrayList<Object>();
		if(CommonUtils.isNotEmpty(vo.getStartTime()) ) {
			values.add(vo.getStartTime());
		}
		if(CommonUtils.isNotEmpty(vo.getEndTime()) ) {
			values.add(vo.getEndTime());
		}
		return query(getCountSql(vo),SimpleRowMapper.createDefault(DealerInventoryDayCount.class),values.toArray());
	}

	private String getCountSql(DealerInventoryPageParam vo) {
		StringBuffer sql = new StringBuffer();
		final String scanCodeErrorTableName = tableName();
		final String productTableName = productDao.tableName();
		String dealerIdsString = new String();
		if(CommonUtils.isNotEmpty(vo.getDealerType())) {
			if(CrmAcctType.DEALER.value.equals(vo.getDealerType())) {
				List<Integer> dealersId = disDealerDao.findFirstChildIdAnSelf(vo.getDealerId());
				dealerIdsString = StringUtils.join(dealersId.toArray(), ",");
			}else if(CrmAcctType.TERMINAL_SHOP.value.equals(vo.getDealerType())) {
				dealerIdsString = vo.getDealerId()+"";
			}
		}
		sql.append("SELECT COUNT (*) AS _count, CONVERT ( CHAR (10), error._scan_time, 120 ) AS _scan_time_day ,product._name,error._scan_id  FROM ");
		sql.append(scanCodeErrorTableName).append(" error ,").append(productTableName).append( " product  WHERE");
		if (CrmAcctType.DEALER.value.equals(vo.getDealerType())) {
			sql.append("  ( error._scan_id  in ( ").append(dealerIdsString).append(" )");
			sql.append(" AND error._scan_type= ").append(vo.getDealerType());
			sql.append(" OR error._dealer_id = ").append(vo.getDealerId()).append("  AND error._scan_type = 3) ");
		}else if (CrmAcctType.TERMINAL_SHOP.value.equals(vo.getDealerType())){
			sql.append("  error._scan_id  in ( ").append(dealerIdsString).append(" )");
			sql.append(" AND error._scan_type= ").append(vo.getDealerType());
		}
		if(CommonUtils.isNotEmpty(vo.getStartTime()) && CommonUtils.isNotEmpty(vo.getEndTime())) {
			sql.append(" AND error._scan_time BETWEEN ?  and  ? ");
		}else {
			if(CommonUtils.isNotEmpty(vo.getStartTime())) {
				sql.append(" AND error._scan_time > ? ");
			}else if(CommonUtils.isNotEmpty(vo.getEndTime())) {
				sql.append(" AND error._scan_time < ? ");
			}
		}
		if(CommonUtils.isNotEmpty(vo.getProductName())) {
			if(vo.getProductName().equals("异常码")) {
				sql.append(" AND error._product_id is null ");
			}else {
				sql.append(" AND error._product_id = product._id ");
				sql.append(" AND product._name LIKE N'%").append(vo.getProductName()).append("%'");
			}
		}
		sql.append(" GROUP BY CONVERT ( CHAR (10), error._scan_time, 120 ) , error._scan_id, product._name ");
		return sql.toString();
	}

	@Override
	public List<ErrorInfo> findInfoList(DealerInventoryPageParam vo) {
		if(CommonUtils.isEmpty(vo.getProductName())) {
			throw new MessageException("查看扫码错误信息必须指定产品名称");
		}
		if(CommonUtils.isEmpty(vo.getStartTime()) || CommonUtils.isEmpty(vo.getEndTime()) ) {
			throw new MessageException("查看扫码错误信息必须指定时间段");
		}
		List<Object> values = new ArrayList<Object>();
		if(CommonUtils.isNotEmpty(vo.getStartTime()) ) {
			values.add(vo.getStartTime());
		}
		if(CommonUtils.isNotEmpty(vo.getEndTime()) ) {
			values.add(vo.getEndTime());
		}
		return query(getInfoSql(vo),SimpleRowMapper.createDefault(ErrorInfo.class),values.toArray());
	}

	private String getInfoSql(DealerInventoryPageParam vo) {
		StringBuffer sql = new StringBuffer();
		final String scanCodeErrorTableName = tableName();
		final String productTableName = productDao.tableName();
		String dealerIdsString = new String();
		if(CommonUtils.isNotEmpty(vo.getDealerType())) {
			if(CrmAcctType.DEALER.value.equals(vo.getDealerType())) {
				List<Integer> dealersId = disDealerDao.findFirstChildIdAnSelf(vo.getDealerId());
				dealerIdsString = StringUtils.join(dealersId.toArray(), ",");
			}else if(CrmAcctType.TERMINAL_SHOP.value.equals(vo.getDealerType())) {
				dealerIdsString = vo.getDealerId()+"";
			}
		}
		sql.append("SELECT error._qr_code, error._error_message FROM ");
		sql.append(scanCodeErrorTableName).append(" error ");
		if(!vo.getProductName().equals("异常码")) {
			sql.append("  , ").append(productTableName).append( " product ");
		}
		sql.append("  WHERE ");
		if (CrmAcctType.DEALER.value.equals(vo.getDealerType())) {
			sql.append("  ( error._scan_id  in ( ").append(dealerIdsString).append(" )");
			sql.append(" AND error._scan_type= ").append(vo.getDealerType());
			sql.append(" OR error._dealer_id = ").append(vo.getDealerId()).append("  AND error._scan_type = 3) ");
		}else if (CrmAcctType.TERMINAL_SHOP.value.equals(vo.getDealerType())){
			sql.append("  error._scan_id  in ( ").append(dealerIdsString).append(" )");
			sql.append(" AND error._scan_type= ").append(vo.getDealerType());
		}
		if(CommonUtils.isNotEmpty(vo.getStartTime()) && CommonUtils.isNotEmpty(vo.getEndTime())) {
			sql.append(" AND error._scan_time BETWEEN ?  and  ? ");
		}else {
			if(CommonUtils.isNotEmpty(vo.getStartTime())) {
				sql.append(" AND error._scan_time > ? ");
			}else if(CommonUtils.isNotEmpty(vo.getEndTime())) {
				sql.append(" AND error._scan_time < ? ");
			}
		}
		if(CommonUtils.isNotEmpty(vo.getProductName())) {
			if(vo.getProductName().equals("异常码")) {
				sql.append(" AND error._product_id is null ");
			}else {
				sql.append(" AND error._product_id = product._id ");
				sql.append(" AND product._name LIKE N'%").append(vo.getProductName()).append("%'");
			}
		}
		return sql.toString();
	}

//	private String getSql(DealerInventoryPageParam vo, String string) {
//		StringBuffer sql = new StringBuffer();
//		final String scanCodeErrorTableName = tableName();
//		final String productTableName = productDao.tableName();
//		String dealerIdsString = new String();
//		if(CommonUtils.isNotEmpty(vo.getDealerType())) {
//			if(CrmAcctType.DEALER.value.equals(vo.getDealerType())) {
//				List<Integer> dealersId = disDealerDao.findFirstChildIdAnSelf(vo.getDealerId());
//				dealerIdsString = StringUtils.join(dealersId.toArray(), ",");
//			}else if(CrmAcctType.TERMINAL_SHOP.value.equals(vo.getDealerType())) {
//				dealerIdsString = vo.getDealerId()+"";
//			}
//		}
//		if(string.equals("info")) {
//			sql.append("SELECT error._seq_num, error._error_message FROM ");
//		}else if(string.equals("count")) {
//			sql.append("SELECT COUNT (*) AS _count, CONVERT ( CHAR (10), error._scan_time, 120 ) AS _scan_time_day ,product._name,error._scan_id  FROM ");
//		}
//		sql.append(scanCodeErrorTableName).append(" error ,").append(productTableName).append( " product  WHERE");
//		if (CrmAcctType.DEALER.value.equals(vo.getDealerType())) {
//			sql.append("  ( error._scan_id  in ( ").append(dealerIdsString).append(" )");
//			sql.append(" AND error._scan_type= ").append(vo.getDealerType());
//			sql.append(" OR error._dealer_id = ").append(vo.getDealerId()).append("  AND error._scan_type = 3) ");
//		}else if (CrmAcctType.TERMINAL_SHOP.value.equals(vo.getDealerType())){
//			sql.append("  error._scan_id  in ( ").append(dealerIdsString).append(" )");
//			sql.append(" AND error._scan_type= ").append(vo.getDealerType());
//		}
//		if(CommonUtils.isNotEmpty(vo.getStartTime()) && CommonUtils.isNotEmpty(vo.getEndTime())) {
//			sql.append(" AND error._scan_time BETWEEN ?  and  ? ");
//		}else {
//			if(CommonUtils.isNotEmpty(vo.getStartTime())) {
//				sql.append(" AND error._scan_time > ? ");
//			}else if(CommonUtils.isNotEmpty(vo.getEndTime())) {
//				sql.append(" AND error._scan_time < ? ");
//			}
//		}
//		sql.append(" AND error._product_id = product._id ");
//		if(CommonUtils.isNotEmpty(vo.getProductName())) {
//			sql.append(" AND product._name LIKE N'%").append(vo.getProductName()).append("%'");
//		}
////		if(string.equals("info")) {
////			sql.append(" GROUP BY CONVERT ( CHAR (10), error._scan_time, 120 ) ");
////		}
//		if(string.equals("count")) {
//			sql.append(" GROUP BY CONVERT ( CHAR (10), error._scan_time, 120 ) , error._scan_id, product._name ");
//		}
//		return sql.toString();
//	}

	@Override
	public String getSelectErrorSql(DealerInventoryPageParam vo) {
		StringBuffer sql = new StringBuffer();
		final String dealerTableName = disDealerDao.tableName();
		final String shopTableName = disTerminalShopDao.tableName();
		final String scanErrorCodeTableName = tableName();
		final String productTableName = productDao.tableName();
		String dealerIdsString = new String();
		if(CommonUtils.isNotEmpty(vo.getDealerType())) {
			if(CrmAcctType.DEALER.value.equals(vo.getDealerType())) {
				List<Integer> dealersId = disDealerDao.findFirstChildIdAnSelf(vo.getDealerId());
				dealerIdsString = StringUtils.join(dealersId.toArray(), ",");
			}else if(CrmAcctType.TERMINAL_SHOP.value.equals(vo.getDealerType())) {
				dealerIdsString = vo.getDealerId()+"";
			}
		}
		sql.append("SELECT 0 AS _count, error._scan_id AS _scan_id , ");
		sql.append("product._name AS _product_name,");
		sql.append( "(CASE WHEN shop._name IS NULL THEN dealer._name ELSE shop._name END)  AS _scan_name,");
		sql.append( "convert(char(10),error._scan_time,120) AS _scan_time_day , 0 AS _price , COUNT (*)  AS _error_num FROM ");
		sql.append(productTableName).append(" product ,");
		sql.append(scanErrorCodeTableName).append(" error ");
		sql.append(" LEFT JOIN ( SELECT _name ,_id FROM ").append(dealerTableName);
		sql.append(" )dealer ON ( error._scan_id = dealer._id AND error._scan_type = 2 )");
		sql.append(" LEFT JOIN ( SELECT _name ,_id FROM ").append(shopTableName);
		sql.append(" )shop ON ( error._scan_id = shop._id AND error._scan_type = 3 ) WHERE");
		if (CrmAcctType.DEALER.value.equals(vo.getDealerType())) {
			sql.append("  ( error._scan_id  in ( ").append(dealerIdsString).append(" )");
			sql.append(" AND error._scan_type= ").append(vo.getDealerType());
			sql.append(" OR error._dealer_id = ").append(vo.getDealerId()).append("  AND error._scan_type = 3) ");
		}else if (CrmAcctType.TERMINAL_SHOP.value.equals(vo.getDealerType())){
			sql.append("  error._scan_id  in ( ").append(dealerIdsString).append(" )");
			sql.append(" AND error._scan_type= ").append(vo.getDealerType());
		}
		sql.append(" AND error._product_id = product._id ");
		if(CommonUtils.isNotEmpty(vo.getStartTime()) && CommonUtils.isNotEmpty(vo.getEndTime())) {
			sql.append(" AND error._scan_time BETWEEN ?  and  ? ");
		}else {
			if(CommonUtils.isNotEmpty(vo.getStartTime())) {
				sql.append(" AND error._scan_time > ? ");
			}else if(CommonUtils.isNotEmpty(vo.getEndTime())) {
				sql.append(" AND error._scan_time < ? ");
			}
		}
		sql.append(" GROUP BY convert(char(10),error._scan_time,120), error._scan_id, product._name, (CASE WHEN shop._name IS NULL THEN dealer._name ELSE shop._name END)  ");
		return sql.toString();
	}

	@Override
	public String getSelectErrorCodeSql(DealerInventoryPageParam vo) {
		StringBuffer sql = new StringBuffer();
		final String dealerTableName = disDealerDao.tableName();
		final String shopTableName = disTerminalShopDao.tableName();
		final String scanErrorCodeTableName = tableName();
		String dealerIdsString = new String();
		if(CommonUtils.isNotEmpty(vo.getDealerType())) {
			if(CrmAcctType.DEALER.value.equals(vo.getDealerType())) {
				List<Integer> dealersId = disDealerDao.findFirstChildIdAnSelf(vo.getDealerId());
				dealerIdsString = StringUtils.join(dealersId.toArray(), ",");
			}else if(CrmAcctType.TERMINAL_SHOP.value.equals(vo.getDealerType())) {
				dealerIdsString = vo.getDealerId()+"";
			}
		}
		sql.append("SELECT 0 AS _count, error._scan_id AS _scan_id , ");
		sql.append(" N'异常码' AS _product_name,");
		sql.append( "(CASE WHEN shop._name IS NULL THEN dealer._name ELSE shop._name END)  AS _scan_name,");
		sql.append( "convert(char(10),error._scan_time,120) AS _scan_time_day , 0 AS _price , COUNT (*)  AS _error_num FROM ");
		sql.append(scanErrorCodeTableName).append(" error ");
		sql.append(" LEFT JOIN ( SELECT _name ,_id FROM ").append(dealerTableName);
		sql.append(" )dealer ON ( error._scan_id = dealer._id AND error._scan_type = 2 )");
		sql.append(" LEFT JOIN ( SELECT _name ,_id FROM ").append(shopTableName);
		sql.append(" )shop ON ( error._scan_id = shop._id AND error._scan_type = 3 ) WHERE");
		if (CrmAcctType.DEALER.value.equals(vo.getDealerType())) {
			sql.append("  ( error._scan_id  in ( ").append(dealerIdsString).append(" )");
			sql.append(" AND error._scan_type= ").append(vo.getDealerType());
			sql.append(" OR error._dealer_id = ").append(vo.getDealerId()).append("  AND error._scan_type = 3) ");
		}else if (CrmAcctType.TERMINAL_SHOP.value.equals(vo.getDealerType())){
			sql.append("  error._scan_id  in ( ").append(dealerIdsString).append(" )");
			sql.append(" AND error._scan_type= ").append(vo.getDealerType());
		}
		sql.append(" AND error._product_id is null ");
		if(CommonUtils.isNotEmpty(vo.getStartTime()) && CommonUtils.isNotEmpty(vo.getEndTime())) {
			sql.append(" AND error._scan_time BETWEEN ?  and  ? ");
		}else {
			if(CommonUtils.isNotEmpty(vo.getStartTime())) {
				sql.append(" AND error._scan_time > ? ");
			}else if(CommonUtils.isNotEmpty(vo.getEndTime())) {
				sql.append(" AND error._scan_time < ? ");
			}
		}
		sql.append(" GROUP BY convert(char(10),error._scan_time,120), error._scan_id,  (CASE WHEN shop._name IS NULL THEN dealer._name ELSE shop._name END)  ");
		return sql.toString();
	}

}
